CREATE TABLE [dbo].[tb_Divisiones]
(
[idDivision] [int] NOT NULL IDENTITY(1, 1),
[Mnemo] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[idSubOrg] [int] NULL,
[Descripcion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EsGrupo] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Curso] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Division] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GrupoCarreras] [int] NOT NULL,
[Turno] [int] NOT NULL,
[FechaAlta] [datetime] NOT NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaBaja] [datetime] NULL,
[CUISE] [int] NOT NULL CONSTRAINT [DF_tb_Divisiones_CUISE] DEFAULT ((99)),
[EsPrivada] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Divpriv] [int] NULL,
[NTUser] [int] NULL CONSTRAINT [DF_tb_Divisiones_NTUser] DEFAULT ([dbo].[sp_usuario](suser_sname())),
[FechaCarga] [datetime] NULL,
[idsarrhh_curso_unidad_educativa_id9] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[BajasLogicastb_Divisiones] ON [dbo].[tb_Divisiones]
FOR UPDATE AS

declare @x int

/*

if update(baja) and (Select baja from inserted)='S' and (Select FechaBaja from inserted) is null BEGIN
	UPDATE tb_Divisiones set FechaBaja = getdate() where idDivision=(Select idDivision from inserted)
END
else
if (update(baja) and (Select baja from inserted)='N')	UPDATE tb_Divisiones set FechaBaja= null where idDivision=(Select idDivision from inserted)

*/
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[generarMnemo] ON [dbo].[tb_Divisiones] 
FOR UPDATE
AS

SET NOCOUNT ON

declare divisiones cursor static for Select i.turno, i.curso, i.division, i.grupocarreras, i.idsuborg, d.turno, d.curso, d.division, d.grupocarreras, d.idsuborg, i.idDivision  from inserted i inner join deleted d on d.iddivision = i.iddivision

declare @Mnemo varchar(50)
declare @cuise int
declare @divisiond varchar(7), @divisioni varchar(7)
declare @cursod varchar(5), @cursoi varchar(5)
declare @grupocarrerasd int, @grupocarrerasi int
declare @idsuborgd int, @idsuborgi int
declare @idDivision int
declare @turnod int, @turnoi int

open divisiones
fetch next from divisiones into @turnoi, @cursoi, @divisioni, @grupocarrerasi, @idsuborgi, @turnod, @cursod, @divisiond, @grupocarrerasd, @idsuborgd, @idDivision

while @@fetch_status = 0
begin
	if (@turnoi <> @turnod) or (@cursoi <> @cursod) or (@divisioni <> @divisiond) or (@grupocarrerasd <> @grupocarrerasi) or (@idsuborgi <> @idsuborgd) begin

		select @cuise = cuise from tb_Organizaciones where idOrganizaciones = (Select org from tb_SubOrganizaciones where idSubOrganizacion = @idSubOrgi)

		Select @Mnemo = convert(varchar, @cuise)+'-'+@cursoi+'-'+@divisioni+'-'+(Select m.mnemo from tb_grupodecarreras  m where idgrupocarrera = @grupocarrerasi)+'-'+(Select t.mnemo from tb_turnos  t where idturno = @turnod)

		update tb_Divisiones set Mnemo = @Mnemo ,cuise=@cuise where idDivision = @idDivision
	end

	fetch next from divisiones into @turnoi, @cursoi, @divisioni, @grupocarrerasi, @idsuborgi, @turnod, @cursod, @divisiond, @grupocarrerasd, @idsuborgd, @idDivision

end

close divisiones
deallocate divisiones

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--CONTROL PARA NO INSERTAR DOS DIVISIONES IGUALES SUPERPUESTAS
CREATE TRIGGER [dbo].[insertDivisiones] ON [dbo].[tb_Divisiones] 
FOR INSERT
AS
SET NOCOUNT ON
declare divisionesInert cursor static for Select i.turno, i.curso, i.division, i.grupocarreras, i.idsuborg, i.idDivision  from inserted i 

declare @Mnemo varchar(50)
declare @cuise int
declare @divisiond varchar(7), @divisioni varchar(7)
declare @cursod varchar(5), @cursoi varchar(5)
declare @grupocarrerasd int, @grupocarrerasi int
declare @idsuborgd int, @idsuborgi int
declare @idDivision int
declare @turnod int, @turnoi int

open divisionesInert
fetch next from divisionesInert into @turnoi, @cursoi, @divisioni, @grupocarrerasi, @idsuborgi, @idDivision

while @@fetch_status = 0
begin
	-- update tb_Divisiones set curso = ltrim(rtrim((Select curso from inserted))), division = ltrim(rtrim((Select division from inserted))) where idDivision = (Select idDivision from inserted)
	select @cuise = cuise from tb_Organizaciones where idOrganizaciones = (Select org from tb_SubOrganizaciones where idSubOrganizacion = @idSubOrgi)
	Select @Mnemo = convert(varchar, @cuise)+'-'+@cursoi+'-'+@divisioni+'-'+(Select m.mnemo from tb_grupodecarreras  m where idgrupocarrera = @grupocarrerasi)+'-'+(Select t.mnemo from tb_turnos  t where idturno = @turnoi)
	update tb_Divisiones set Mnemo = @Mnemo, cuise=@cuise where idDivision = @idDivision

	fetch next from divisionesInert into @turnoi, @cursoi, @divisioni, @grupocarrerasi, @idsuborgi, @idDivision

end

close divisionesInert
deallocate divisionesInert



GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatetb_Divisiones] ON [dbo].[tb_Divisiones]
 FOR UPDATE AS 

declare @x int
/*

 IF (update(idDivision)) begin 
	 SELECT * FROM inserted 
	 IF @@ROWCOUNT = 1 begin 
		 UPDATE tb_MatriculasDivision
		 SET Division = (SELECT idDivision FROM inserted)
		 WHERE tb_MatriculasDivision.Division = (SELECT idDivision FROM deleted)
		 UPDATE tb_RelDivisionGrupoSimples
		 SET idDivision2 = (SELECT idDivision FROM inserted)
		 WHERE tb_RelDivisionGrupoSimples.idDivision2 = (SELECT idDivision FROM deleted)
		 UPDATE tb_RelDivisionGrupoSimples
		 SET idDivision = (SELECT idDivision FROM inserted)
		 WHERE tb_RelDivisionGrupoSimples.idDivision = (SELECT idDivision FROM deleted)
		 UPDATE tb_AperturasDeReestructura
		 SET Division = (SELECT idDivision FROM inserted)
		 WHERE tb_AperturasDeReestructura.Division = (SELECT idDivision FROM deleted)
		 UPDATE tb_RelAlumnoDivision
		 SET Division = (SELECT idDivision FROM inserted)
		 WHERE tb_RelAlumnoDivision.Division = (SELECT idDivision FROM deleted)
		 UPDATE tb_CarrerasAlumnos
		 SET Carrera = (SELECT idDivision FROM inserted)
		 WHERE tb_CarrerasAlumnos.Carrera = (SELECT idDivision FROM deleted)
		 UPDATE tb_Cursadas
		 SET Carrera = (SELECT idDivision FROM inserted)
		 WHERE tb_Cursadas.Carrera = (SELECT idDivision FROM deleted)
		 UPDATE tb_Plazas
		 SET Division = (SELECT idDivision FROM inserted)
		 WHERE tb_Plazas.Division = (SELECT idDivision FROM deleted)
	end  ELSE begin 
		 ROLLBACK TRANSACTION 
 end 
 end

*/

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updCUPOF] ON [dbo].[tb_Divisiones] 
FOR UPDATE
AS
SET NOCOUNT ON
declare @x int

if (update(curso)) or (update(division)) or (update(grupocarreras)) or (update(turno))
begin
 update tb_Plazas set turno = (Select turno from inserted), cupof = null where division = (select idDivision from inserted)
end



GO
ALTER TABLE [dbo].[tb_Divisiones] ADD CONSTRAINT [PK_tb_Divisiones] PRIMARY KEY NONCLUSTERED  ([idDivision]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Divisiones] WITH NOCHECK ADD CONSTRAINT [FK_tb_Divisiones_tb_GrupoDeCarreras] FOREIGN KEY ([GrupoCarreras]) REFERENCES [dbo].[tb_GrupoDeCarreras] ([idGrupoCarrera])
GO
ALTER TABLE [dbo].[tb_Divisiones] WITH NOCHECK ADD CONSTRAINT [FK_tb_Divisiones_tb_SubOrganizaciones] FOREIGN KEY ([idSubOrg]) REFERENCES [dbo].[tb_SubOrganizaciones] ([idSubOrganizacion])
GO
ALTER TABLE [dbo].[tb_Divisiones] WITH NOCHECK ADD CONSTRAINT [FK_tb_Divisiones_tb_Turnos] FOREIGN KEY ([Turno]) REFERENCES [dbo].[tb_Turnos] ([idTurno])
GO
EXEC sp_bindrule N'[dbo].[S-N]', N'[dbo].[tb_Divisiones].[EsGrupo]'
GO
EXEC sp_bindrule N'[dbo].[S-N]', N'[dbo].[tb_Divisiones].[Baja]'
GO
EXEC sp_bindrule N'[dbo].[S-N]', N'[dbo].[tb_Divisiones].[CUISE]'
GO
